import sys
from PyQt5.Qt import *
from BMI对比公式 import bmi
from PyQt5 import QtGui, QtCore
import threading
from openpyxl.styles import Font, Alignment
import datetime
import openpyxl
from docx import Document
from docx.shared import Pt, Cm, Mm
from docx.oxml.ns import qn
from docx.enum.text import WD_PARAGRAPH_ALIGNMENT
from PyQt5.QtWidgets import QFileDialog, QGraphicsObject
import time


class School(QMainWindow, QGraphicsObject):
    # 定义信号1 作用触发 携带信息为字符串 使用线程必须使用信号辅助 防止线程资源争夺 导致程序卡死崩溃
    signal1 = QtCore.pyqtSignal(str)
    # 定义信号2 作用触发 携带信息为字符串 使用线程必须使用信号辅助 防止线程资源争夺 导致程序卡死崩溃
    signal2 = QtCore.pyqtSignal(str)

    def __init__(self):
        super().__init__()
        # 获取当前时间
        self.starttime = datetime.datetime.today()
        # 全路径
        self.filepath1 = ""
        # 仅文件名
        self.fileName1 = ""
        self.setFixedSize(1400, 900)
        self.setWindowTitle("学校体检数据便捷版处理程序")
        self.setui_UI()
        # 信号一与self.textBrowser捆绑 用来传输文字信息
        self.signal1.connect(self.Qtext_B)
        # 信号一与self.textBrowser_2捆绑 用来传输文字信息
        self.signal2.connect(self.Qtext_C)
        self.fangwei()

    def setui_UI(self):
        # 设置字体控件
        font = QtGui.QFont()
        font.setFamily("Adobe Arabic")
        font.setPointSize(16)
        # 信息显示框1
        self.QTextA = QTextBrowser(self)
        self.QTextA.setText("运行记录")
        self.QTextA.setFont(font)
        self.QTextA.resize(400, 350)
        self.QTextA.move(50, 50)
        # 信息显示框2
        self.QTextB = QTextBrowser(self)
        self.QTextB.setText("错误记录")
        self.QTextB.setFont(font)
        self.QTextB.resize(400, 350)
        self.QTextB.move(50, 450)
        # 视力标准按钮
        self.Qbox = QCheckBox(self)
        self.Qbox.move(650, 220)

        # 视力参与评价开关
        self.Qbox1 = QCheckBox(self)
        self.Qbox1.move(650, 80)
        self.Qbox1.setFont(font)
        self.Qbox1.setText("视力评价开关")

        # 视力标准按钮注释11
        self.lableSL11 = QLabel(self)
        self.lableSL11.setText("选中参与不选中不参与")
        self.lableSL11.resize(400, 30)
        self.lableSL11.setFont(font)
        self.lableSL11.move(600, 130)

        # 视力标准按钮标注
        self.lableSL = QLabel(self)
        self.lableSL.setText("视力标准")
        self.lableSL.setFont(font)
        self.lableSL.move(670, 220)
        # 视力标准按钮注释1
        self.lableSL1 = QLabel(self)
        self.lableSL1.setText("未选中将采用中国视力标准5.0视力表")
        self.lableSL1.resize(400, 30)
        self.lableSL1.setFont(font)
        self.lableSL1.move(560, 250)
        # 视力标准按钮注释2
        self.lableSL2 = QLabel(self)
        self.lableSL2.setText("选中将采用国际视力标准1.5视力表")
        self.lableSL2.resize(400, 30)
        self.lableSL2.setFont(font)
        self.lableSL2.move(560, 280)

        self.btn1 = QPushButton(self)
        self.btn1.setText("打开文件")
        self.btn1.setFont(font)
        self.btn1.resize(100, 40)
        self.btn1.move(1100, 90)

        self.btn2 = QPushButton(self)
        self.btn2.setText("检查录入")
        self.btn2.setFont(font)
        self.btn2.resize(100, 40)
        self.btn2.move(1100, 150)

        self.btn3 = QPushButton(self)
        self.btn3.setText("进行计算")
        self.btn3.setFont(font)
        self.btn3.resize(100, 40)
        self.btn3.move(1100, 210)

        self.btn4 = QPushButton(self)
        self.btn4.setText("生成附件")
        self.btn4.setFont(font)
        self.btn4.resize(100, 40)
        self.btn4.move(1100, 270)

        self.btn5 = QPushButton(self)
        self.btn5.setText("追加汇总数据")
        self.btn5.setFont(font)
        self.btn5.resize(140, 40)
        self.btn5.move(1100, 330)

        self.btn6 = QPushButton(self)
        self.btn6.setText("生成报告")
        self.btn6.setFont(font)
        self.btn6.resize(140, 40)
        self.btn6.move(1100, 390)

        self.btn7 = QPushButton(self)
        self.btn7.setText("数据分离")
        self.btn7.setFont(font)
        self.btn7.resize(140, 40)
        self.btn7.move(1100, 450)

        self.btn8 = QPushButton(self)
        self.btn8.setText("数据统计")
        self.btn8.setFont(font)
        self.btn8.resize(140, 40)
        self.btn8.move(1100, 510)

        self.btn9 = QPushButton(self)
        self.btn9.setText("写入数据")
        self.btn9.setFont(font)
        self.btn9.resize(140, 40)
        self.btn9.move(1100, 570)

        self.btn10 = QPushButton(self)
        self.btn10.setText("填充")
        self.btn10.setFont(font)
        self.btn10.resize(140, 40)
        self.btn10.move(1100, 630)

        self.label1 = QLabel(self)
        self.label1.setText("第一步")
        self.label1.setFont(font)
        self.label1.move(1000, 95)

        self.label2 = QLabel(self)
        self.label2.setText("第二步")
        self.label2.setFont(font)
        self.label2.move(1000, 155)

        self.label3 = QLabel(self)
        self.label3.setText("第三步")
        self.label3.setFont(font)
        self.label3.move(1000, 215)

        self.label4 = QLabel(self)
        self.label4.setText("第四步")
        self.label4.setFont(font)
        self.label4.move(1000, 275)

        self.label5 = QLabel(self)
        self.label5.setText("第五步")
        self.label5.setFont(font)
        self.label5.move(1000, 335)

        self.label6 = QLabel(self)
        self.label6.setText("第六步")
        self.label6.setFont(font)
        self.label6.move(1000, 395)

        self.label13 = QLabel(self)
        self.label13.setText("第七步")
        self.label13.setFont(font)
        self.label13.move(1000, 455)

        self.label14 = QLabel(self)
        self.label14.setText("第八步")
        self.label14.setFont(font)
        self.label14.move(1000, 515)

        self.label15 = QLabel(self)
        self.label15.setText("第九步")
        self.label15.setFont(font)
        self.label15.move(1000, 575)

        self.label16 = QLabel(self)
        self.label16.setText("第十步")
        self.label16.setFont(font)
        self.label16.move(1000, 635)

        self.label7 = QLabel(self)
        self.label7.setText("报告参数")
        self.label7.setFont(font)
        self.label7.move(680, 320)

        self.label8 = QLabel(self)
        self.label8.setText("进行第六步之前必填")
        self.label8.setFont(font)
        self.label8.resize(350, 30)
        self.label8.move(630, 360)

        self.label9 = QLabel(self)
        self.label9.setText("受检单位")
        self.label9.setFont(font)
        self.label9.resize(150, 30)
        self.label9.move(550, 450)

        self.label10 = QLabel(self)
        self.label10.setText("体检机构")
        self.label10.setFont(font)
        self.label10.resize(150, 30)
        self.label10.move(550, 500)

        self.label11 = QLabel(self)
        self.label11.setText("健康顾问")
        self.label11.setFont(font)
        self.label11.resize(150, 30)
        self.label11.move(550, 550)

        self.label12 = QLabel(self)
        self.label12.setText("体检日期")
        self.label12.setFont(font)
        self.label12.resize(150, 30)
        self.label12.move(550, 600)

        self.lin1 = QLineEdit(self)
        self.lin1.setText("请输入受检单位")
        self.lin1.setFont(font)
        self.lin1.resize(300, 30)
        self.lin1.move(650, 448)

        self.lin2 = QLineEdit(self)
        self.lin2.setText("请输入体检机构")
        self.lin2.setFont(font)
        self.lin2.resize(300, 30)
        self.lin2.move(650, 500)

        self.lin3 = QLineEdit(self)
        self.lin3.setText("请输入健康顾问")
        self.lin3.setFont(font)
        self.lin3.resize(300, 30)
        self.lin3.move(650, 550)

        self.lin4 = QLineEdit(self)
        self.lin4.setText("请输入联系电话")
        self.lin4.setFont(font)
        self.lin4.resize(300, 30)
        self.lin4.move(650, 600)

    def fangwei(self):

        with open('config.inf', 'r') as f:
            file1 = f.read()
            file = file1.split('=')
            file2 = ''.join(file[1])
            key1 = file2[-4:-2]
            key = int(key1) - 1
            if key < 10:
                if key <= 0:
                    key2 = '00'
                    file4 = file2.replace(file2[-4:-2], key2)
                else:
                    key2 = '0' + str(key)
                    file4 = file2.replace(file2[-4:-2], key2)
            else:
                file4 = file2.replace(file2[-4:-2], str(key))

        with open('config.inf', 'w') as f:
            f.write(f'MD5 ' + '=' + file4)
            f.close()

        if starttime < datetime.datetime(2023, 11, 1) and key > 0:
            self.btn1.clicked.connect(self.open_file)
            self.btn2.clicked.connect(lambda: self.thread_j(self.jiancha))
            self.btn3.clicked.connect(lambda: self.thread_j(self.jisuan))
            self.btn4.clicked.connect(lambda: self.thread_j(self.fujian))
            self.btn5.clicked.connect(lambda: self.thread_j(self.Sum_result))
            self.btn6.clicked.connect(lambda: self.thread_j(self.Baogao))
            self.btn7.clicked.connect(lambda: self.thread_j(self.data_fl))
            self.btn8.clicked.connect(lambda: self.thread_j(self.data_tj))
            self.btn9.clicked.connect(lambda: self.thread_j(self.data_wr))
            self.btn10.clicked.connect(lambda: self.thread_j(self.tc))

        else:
            self.btn1.setEnabled(False)
            self.btn2.setEnabled(False)
            self.btn3.setEnabled(False)
            self.btn4.setEnabled(False)
            self.btn5.setEnabled(False)
            self.btn6.setEnabled(False)
            self.btn7.setEnabled(False)
            self.btn8.setEnabled(False)
            self.btn9.setEnabled(False)
            self.btn10.setEnabled(False)

    def thread_j(self, massage):
        t = threading.Thread(target=massage, daemon=True)
        t.start()

    def open_file(self):
        """
        按钮打开文件获取文件名及文件路径
        """
        filename, filetype = QFileDialog.getOpenFileName(self, "选取Excel文件", "./data", "Excel Files (*.xls *.xlsx)")
        self.Qtext_B(f'打开文件路径{filename}')
        # 将全局变量用来存储 文件路径
        self.filepath = filename
        # 将全局变量用来存储 处理以后的文件名
        self.fileName = self.filepath.split('/')[-1].split('.')[0]

    def Qtext_B(self, *args):
        """
        文本框 实时显示自定义函数
        """
        self.QTextA.append(*args)  # 在指定的区域显示提示信息

    def Qtext_C(self, message):
        """
        文本框 实时显示自定义函数
        """
        data_time = str(datetime.datetime.now())
        # 在指定的区域显示提示信息
        self.QTextB.append(message)
        with open(f"./log/log.txt", 'a', encoding='utf-8') as file:
            file.write(f'{data_time}——→{message}')
            file.write('\n')
            file.close()

    def fashe(self, zidan):
        """
        发射信号函数 使信号与传递的值进行绑定携带
        """
        self.signal1.emit(zidan)

    def fashe2(self, zidan):
        """
                发射信号函数 使信号与传递的值进行绑定携带
                """
        self.signal2.emit(zidan)

    # 检查录入模块 已开发完毕 注意检查生成表格文件
    def jiancha(self):
        """
        主要作用检查 到检情况 年龄 身高 体重 视力参数输入是否正确
        """
        workbook = openpyxl.load_workbook(self.filepath)
        self.Qtext_B(f'开始处理文件{self.filepath}')
        # 获取体检数据表名[列表形式]
        books = workbook.sheetnames
        for page in books:
            self.fashe(f"开始处理{page}")
            sheet = workbook[f'{page}']
            row = sheet.max_row
            for i in range(5, row + 1):
                if sheet[f'G{i}'].value is None:
                    self.fashe2(f"{page}{sheet[f'C{i}'].value}{f'{i}'}行到检数据异常，请检查！")
                    pass
                elif sheet[f'G{i}'].value == 0:
                    pass
                elif isinstance(sheet[f'G{i}'].value, str):
                    try:
                        float(sheet[f'G{i}'].value)
                    except ValueError:
                        self.fashe2(f"{page}{sheet[f'C{i}'].value}{f'{i}'}行到检数据不能填写汉字！")
                elif int(sheet[f'G{i}'].value) > 1:
                    self.fashe2(f"{page}{sheet[f'D{i}'].value}{f'{i}'}行到检数据只接受 1或者0 到检或者未到检")
                else:
                    # 检查身份证号码
                    if sheet[f'D{i}'].value is None:
                        if sheet[f'F{i}'].value is None:
                            self.fashe2(f"{page}{sheet[f'C{i}'].value}{f'{i}'}行身份证号码没有！")
                        else:
                            pass
                    else:
                        if len(str(sheet[f'D{i}'].value)) < 18:
                            self.fashe2(f"{page}{sheet[f'C{i}'].value}{f'{i}'}行身份证号码不足18位！")
                        else:
                            pass

                    # 检查性别是否已填 已编写完毕
                    if sheet[f'E{i}'].value == '男' or sheet[f'E{i}'].value == '女':
                        pass
                    else:
                        self.fashe2(f"{page}{sheet[f'C{i}'].value}{f'{i}'}行性别异常请注意检查！")

                    # 检查年龄是否已填 已编写完毕
                    if sheet[f'D{i}'].value is None:
                        if sheet[f'F{i}'].value is None:
                            self.fashe2(f"{page}{sheet[f'C{i}'].value}{f'{i}'}行身份证号码与年龄都没有")
                    else:
                        if isinstance(sheet[f'F{i}'].value, str):
                            try:
                                float(sheet[f'F{i}'].value)
                            except ValueError:
                                self.fashe2(f"{page}{sheet[f'C{i}'].value}{f'{i}'}行年龄不能填写汉字")
                        else:
                            pass

                    # 检查身高录入情况
                    if sheet[f'K{i}'].value is None:
                        self.fashe2(f"{page}{sheet[f'C{i}'].value}{f'{i}'}行身高未填请检查！")
                    elif isinstance(sheet[f'K{i}'].value, str):
                        try:
                            float(sheet[f'K{i}'].value)
                        except ValueError:
                            self.fashe2(f"{page}{sheet[f'C{i}'].value}{f'{i}'}行身高不能填写汉字 未参检 请标红字！")
                    elif float(sheet[f'K{i}'].value) > 10:
                        self.fashe2(f"{page}{sheet[f'C{i}'].value}{f'{i}'}行身高{sheet[f'K{i}'].value}请注意检查！")
                    else:
                        pass

                    # 检查体重录入
                    if sheet[f'L{i}'].value is None:
                        self.fashe2(f"{page}{sheet[f'C{i}'].value}{f'{i}'}行请核实体重是否参检！")
                    elif isinstance(sheet[f'L{i}'].value, str):
                        try:
                            float(sheet[f'L{i}'].value)
                        except ValueError:
                            self.fashe2(f"{page}{sheet[f'C{i}'].value}{f'{i}'}行身高不能填写汉字 未参检 请标红字！")
                    elif float(sheet[f'L{i}'].value) > 150:
                        self.fashe2(f"{page}{sheet[f'C{i}'].value}{f'{i}'}行体重{sheet[f'L{i}'].value}请注意检查！")
                    else:
                        pass

                    if sheet[f'M{i}'].value is None:
                        self.fashe2(f"{page}{sheet[f'C{i}'].value}{f'{i}'}行胸围异常，请检查！")

                    if sheet[f'N{i}'].value is None:
                        self.fashe2(f"{page}{sheet[f'C{i}'].value}{f'{i}'}行肺活量异常，请检查！")

                    if sheet[f'O{i}'].value is None:
                        self.fashe2(f"{page}{sheet[f'C{i}'].value}{f'{i}'}行血压异常，请检查！")

                    if sheet[f'P{i}'].value is None:
                        self.fashe2(f"{page}{sheet[f'C{i}'].value}{f'{i}'}行脉搏异常，请检查！")

                    if self.Qbox1.isChecked():

                        if self.Qbox.isChecked():
                            #  国际视力 判断左眼
                            if sheet[f'Q{i}'].value is None:
                                self.fashe2(f"{page}{sheet[f'C{i}'].value}{f'{i}'}行左眼视力未填！未参与可不填")
                            elif type(sheet[f'Q{i}'].value) == str:
                                if isinstance(sheet[f'Q{i}'].value, str):
                                    try:
                                        float(sheet[f'Q{i}'].value)
                                    except ValueError:
                                        self.fashe2(f"{page}{sheet[f'C{i}'].value}{f'{i}'}行视力左眼不能填写汉字！")
                            elif isinstance(sheet[f'Q{i}'].value, (int, float)):
                                if float(sheet[f'Q{i}'].value) > 2.0:
                                    self.fashe2(f"{page}{sheet[f'C{i}'].value}{f'{i}'}行视力读数异常，请注意视力标准选择！")
                            else:
                                pass
                                #  国际视力 判断右眼
                                if sheet[f'T{i}'].value is None:
                                    self.fashe2(f"{page}{sheet[f'C{i}'].value}{f'{i}'}行左眼视力未填！未参与可不填")
                                elif type(sheet[f'T{i}'].value) == str:
                                    if isinstance(sheet[f'T{i}'].value, str):
                                        try:
                                            float(sheet[f'T{i}'].value)
                                        except ValueError:
                                            self.fashe2(f"{page}{sheet[f'C{i}'].value}{f'{i}'}行视力左眼不能填写汉字！")
                                elif isinstance(sheet[f'T{i}'].value, (int, float)):
                                    if float(sheet[f'T{i}'].value) > 2.0:
                                        self.fashe2(f"{page}{sheet[f'C{i}'].value}{f'{i}'}行视力读数异常，请注意视力标准选择！")
                                else:
                                    pass
                        else:
                            #  国际视力 判断左眼
                            if sheet[f'Q{i}'].value is None:
                                self.fashe2(f"{page}{sheet[f'C{i}'].value}{f'{i}'}行左眼视力未填！未参与可不填")
                            elif type(sheet[f'Q{i}'].value) == str:
                                if isinstance(sheet[f'Q{i}'].value, str):
                                    try:
                                        float(sheet[f'Q{i}'].value)
                                    except ValueError:
                                        self.fashe2(f"{page}{sheet[f'C{i}'].value}{f'{i}'}行视力左眼不能填写汉字！")
                            elif isinstance(sheet[f'Q{i}'].value, (int, float)):
                                if float(sheet[f'Q{i}'].value) < 3.0:
                                    self.fashe2(f"{page}{sheet[f'C{i}'].value}{f'{i}'}行视力读数异常，请注意视力标准选择！")
                            else:
                                pass
                                #  国际视力 判断右眼
                                if sheet[f'T{i}'].value is None:
                                    self.fashe2(f"{page}{sheet[f'C{i}'].value}{f'{i}'}行左眼视力未填！未参与可不填")
                                elif type(sheet[f'T{i}'].value) == str:
                                    if isinstance(sheet[f'T{i}'].value, str):
                                        try:
                                            float(sheet[f'T{i}'].value)
                                        except ValueError:
                                            self.fashe2(f"{page}{sheet[f'C{i}'].value}{f'{i}'}行视力左眼不能填写汉字！")
                                elif isinstance(sheet[f'T{i}'].value, (int, float)):
                                    if float(sheet[f'T{i}'].value) < 3.0:
                                        self.fashe2(f"{page}{sheet[f'C{i}'].value}{f'{i}'}行视力读数异常，请注意视力标准选择！")
                                else:
                                    pass
                    else:
                        pass

                    if sheet[f'W{i}'].value is None or sheet[f'X{i}'].value is None:
                        self.fashe2(f"{page}{sheet[f'C{i}'].value}{f'{i}'}行听力异常，请检查！")
                    else:
                        try:
                            float(sheet[f'W{i}'].value)
                            float(sheet[f'X{i}'].value)
                        except ValueError:
                            self.fashe2(f"{page}{sheet[f'C{row}'].value}听力不能填写汉字！")
        self.Qtext_B(f'处理文件{self.filepath}完毕！')

    # 数据计算 已开发完毕
    def jisuan(self):
        """
        根据电子化数据计算 BMI、BMI修正值、身高对比结果、视力对比结果
        """
        # 读取体检数据文件
        workbook = openpyxl.load_workbook(f"{self.filepath}")
        self.fashe(f"开始处理{self.filepath}")
        # 获取体检数据表名[列表形式]
        books = workbook.sheetnames
        for page in books:
            sheet = workbook[f'{page}']
            # 获取最大行数
            max_row = sheet.max_row
            for i in range(5, max_row + 1):
                # 自动填写班级名称
                sheet[f'B{i}'].value = sheet.title
                # 计算年龄
                if sheet[f'F{i}'].value is not None:
                    pass
                else:
                    if sheet[f'D{i}'].value is None or sheet[f'D{i}'].value == '身份证号' or sheet[f'D{i}'].value == 0:
                        pass
                    else:
                        if len(sheet[f'D{i}'].value) < 15:
                            self.fashe2(f"{sheet}{sheet[f'C{i}'].value}{i}行身份证数据异常")
                        else:
                            sheet[f'F{i}'].value = 2023 - int(sheet[f'D{i}'].value[6:10])

                # BMI计算
                if sheet[f'L{i}'].value is None or sheet[f'K{i}'].value is None:
                    pass
                else:
                    sheet[f'AT{i}'].value = bmi.BMI_result2(sheet[f'L{i}'].value, sheet[f'K{i}'].value)

                # BMI男女对比
                if sheet[f'AT{i}'].value:
                    if sheet[f'E{i}'].value == '男':
                        if sheet[f'F{i}'].value is None:
                            pass
                        else:
                            sheet[f'AU{i}'].value = bmi.hight_Bmi_Man(sheet[f'F{i}'].value, sheet[f'AT{i}'].value)
                    else:
                        if sheet[f'F{i}'].value is None:
                            pass
                        else:
                            sheet[f'AU{i}'].value = bmi.hight_Bmi_Woman(sheet[f'F{i}'].value, sheet[f'AT{i}'].value)

                # 判断身高
                if sheet[f'K{i}'].value:
                    if sheet[f'E{i}'].value == '男':
                        if sheet[f'F{i}'].value is None:
                            pass
                        else:
                            sheet[f'AV{i}'].value = bmi.hight_man(sheet[f'F{i}'].value,
                                                                  round(float(sheet[f'K{i}'].value) / 100, 3))
                    else:
                        if sheet[f'F{i}'].value is None:
                            pass
                        else:
                            sheet[f'AV{i}'].value = bmi.hight_woman(sheet[f'F{i}'].value,
                                                                        round(float(sheet[f'K{i}'].value) / 100, 3))
                # 处理视力评价
                if self.Qbox1.isChecked():
                    if sheet[f'Q{i}'].value and sheet[f'T{i}'].value:
                        if self.Qbox.isChecked():
                            if float(sheet[f'Q{i}'].value) > float(sheet[f'T{i}'].value):
                                sheet[f'AW{i}'].value = bmi.gjeyesight1(float(sheet[f'T{i}'].value))
                            elif float(sheet[f'Q{i}'].value) < float(sheet[f'T{i}'].value):
                                sheet[f'AW{i}'].value = bmi.gjeyesight1(float(sheet[f'Q{i}'].value))
                            else:
                                sheet[f'AW{i}'].value = bmi.gjeyesight1(float(sheet[f'T{i}'].value))
                        else:
                            if float(sheet[f'Q{i}'].value) > float(sheet[f'T{i}'].value):
                                sheet[f'AW{i}'].value = bmi.zgeyesight2(float(sheet[f'T{i}'].value))
                            elif float(sheet[f'Q{i}'].value) < float(sheet[f'T{i}'].value):
                                sheet[f'AW{i}'].value = bmi.zgeyesight2(float(sheet[f'Q{i}'].value))
                            else:
                                sheet[f'AW{i}'].value = bmi.zgeyesight2(float(sheet[f'T{i}'].value))
        workbook.save(f'./report/{self.fileName}体检数据明细表.xlsx')
        workbook.close()
        return self.fashe(f'{self.fileName}体检数据明细表.xlsx 处理完毕')

    # 附件数据采集 已开发完毕
    def fujian(self):
        # 读取体检数据文件
        workbook = openpyxl.load_workbook(f"./report/{self.fileName}体检数据明细表.xlsx")
        self.fashe(f"开始处理{self.filepath}")
        # 获取体检数据表名[列表形式]
        books = workbook.sheetnames
        for page in books:
            # 班级名称
            banji = page
            # 班级学生总人数
            students = bmi.count(workbook[f'{page}']['C'])
            # 实际到检人数
            daojians = bmi.daojiancount(workbook[f'{page}']['G'])
            # 实际未参检人数
            nodaojians = students - daojians
            # 班级检查率
            jcl = bmi.baifenbi(daojians, students)
            # 附录1 参数
            data1 = {
                'banji': banji,
                'students': students,
                'daojians': daojians,
                'nodaojians': nodaojians,
                'jcl': jcl,
            }
            # 将数据以字典形式发送至附件一并进行录入保存
            bmi.wirte_f1(data1)
            # 反馈数据到信息框
            self.fashe(f"{banji}数据已写入附录一")
            # 心脏异常人数
            xzyc = bmi.count(workbook[f'{page}']['AC'])
            xzyc_l = bmi.baifenbi(xzyc, daojians)
            # 肺部异常人数
            fbyc = bmi.count(workbook[f'{page}']['AD'])
            fbyc_l = bmi.baifenbi(fbyc, daojians)
            # 肝脾异常人数
            gpyc = bmi.count(workbook[f'{page}']['AE'])
            gpyc_l = bmi.baifenbi(gpyc, daojians)
            # 头部异常人数
            tbyc = bmi.count(workbook[f'{page}']['AO'])
            tbyc_l = bmi.baifenbi(tbyc, daojians)
            # 颈 部异常人数
            jbyc = bmi.count(workbook[f'{page}']['AP'])
            jbyc_l = bmi.baifenbi(jbyc, daojians)
            # 胸部异常人数
            xbyc = bmi.count(workbook[f'{page}']['AQ'])
            xbyc_l = bmi.baifenbi(xbyc, daojians)
            # 脊柱异常人数
            jzyc = bmi.count(workbook[f'{page}']['AG'])
            jzyc_l = bmi.baifenbi(jzyc, daojians)
            # 四肢异常人数
            szyc = bmi.count(workbook[f'{page}']['AH'])
            szyc_l = bmi.baifenbi(szyc, daojians)
            # 皮肤异常人数
            pfyc = bmi.count(workbook[f'{page}']['AH'])
            pfyc_l = bmi.baifenbi(pfyc, daojians)
            # 淋巴异常人数
            lbyc = bmi.count(workbook[f'{page}']['AH'])
            lbyc_l = bmi.baifenbi(lbyc, daojians)
            # 附录2 参数
            data2 = {
                'banji': banji,
                'daojians': daojians,
                'xzyc': xzyc,
                'xzyc_l': xzyc_l,
                'fbyc': fbyc,
                'fbyc_l': fbyc_l,
                'gpyc': gpyc,
                'gpyc_l': gpyc_l,
                'tbyc': tbyc,
                'tbyc_l': tbyc_l,
                'jbyc': jbyc,
                'jbyc_l': jbyc_l,
                'xbyc': xbyc,
                'xbyc_l': xbyc_l,
                'jzyc': jzyc,
                'jzyc_l': jzyc_l,
                'szyc': szyc,
                'szyc_l': szyc_l,
                'pfyc': pfyc,
                'pfyc_l': pfyc_l,
                'lbyc': lbyc,
                'lbyc_l': lbyc_l,
            }
            # 将数据以字典形式发送至附件一并进行录入保存
            bmi.wirte_f2(data2)
            # 反馈数据到信息框
            self.fashe(f"{banji}数据已写入附录二")

            # 沙眼异常人数
            syyc = bmi.count(workbook[f'{page}']['Y'])
            syyc_l = bmi.baifenbi(syyc, daojians)

            # 牙周异常人数
            yzyc = bmi.count(workbook[f'{page}']['AB'])
            yzyc_l = bmi.baifenbi(yzyc, daojians)

            # 鼻子异常人数
            bbyc = bmi.count(workbook[f'{page}']['AA'])
            bbyc_l = bmi.baifenbi(bbyc, daojians)

            # 扁桃体周异常人数
            bttyc = bmi.count(workbook[f'{page}']['AS'])
            bttyc_l = bmi.baifenbi(bttyc, daojians)

            # 视力不良人数判定
            slyc = bmi.shilicount(workbook[f'{page}']['AW'])
            qsl = slyc['qsl']
            qsl_l = bmi.baifenbi(qsl, daojians)
            zsl = slyc['zsl']
            zsl_l = bmi.baifenbi(zsl, daojians)
            zdsl = slyc['zdsl']
            zdsl_l = bmi.baifenbi(zdsl, daojians)

            # 耳异常人数
            eryc = bmi.count(workbook[f'{page}']['AX'])
            eryc_l = bmi.baifenbi(eryc, daojians)

            # 龋齿人数
            qcyc = bmi.count(workbook[f'{page}']['AY'])
            qcyc_l = bmi.baifenbi(qcyc, daojians)

            # 附录3 参数
            data3 = {
                'banji': banji,
                'daojians': daojians,
                'syyc': syyc,
                'syyc_l': syyc_l,
                'yzyc': yzyc,
                'yzyc_l': yzyc_l,
                'bbyc': bbyc,
                'bbyc_l': bbyc_l,
                'bttyc': bttyc,
                'bttyc_l': bttyc_l,
                'qsl': qsl,
                'qsl_l': qsl_l,
                'zsl': zsl,
                'zsl_l': zsl_l,
                'zdsl': zdsl,
                'zdsl_l': zdsl_l,
                'eryc': eryc,
                'eryc_l': eryc_l,
                'qcyc': qcyc,
                'qcyc_l': qcyc_l,
            }
            bmi.wirte_f3(data3)
            self.fashe(f"{banji}数据已写入附录三")

            # 性别人数
            gender = bmi.gender(workbook[f'{page}']['E'])
            man = gender['man']
            woman = gender['women']
            # 判断男性女性营养吸收效果
            shenzhang = bmi.shengzhang(zip(workbook[f'{page}']["E"], workbook[f'{page}']["AU"]))
            manxs = shenzhang['男性消瘦']
            mancz = shenzhang['男性超重']
            manfp = shenzhang['男性肥胖']
            womenxs = shenzhang['女性消瘦']
            womencz = shenzhang['女性超重']
            womenfp = shenzhang['女性肥胖']
            # 采集身高生长迟缓数据
            chihuan = bmi.shengaojieguo(zip(workbook[f'{page}']["E"], workbook[f'{page}']["AV"]))
            nanxingch = chihuan['man']
            nvxingch = chihuan['woman']

            # 附录4 参数
            data4 = {
                'banji': banji,
                'students': students,
                'daojians': daojians,
                'nodaojians': nodaojians,
                'man': man,
                'woman': woman,
                'manxs': manxs,
                'mancz': mancz,
                'manfp': manfp,
                'nanxingch': nanxingch,
                'womenxs': womenxs,
                'womencz': womencz,
                'womenfp': womenfp,
                'nvxingch': nvxingch,
                'qsl': qsl,
                'zsl': zsl,
                'zdsl': zdsl,
                'xzyc': xzyc,
                'fbyc': fbyc,
                'gpyc': gpyc,
                'tbyc': tbyc,
                'jbyc': jbyc,
                'xbyc': xbyc,
                'jzyc': jzyc,
                'szyc': szyc,
                'pfyc': pfyc,
                'lbyc': lbyc,
                'eryc': eryc,
                'bbyc': bbyc,
                'bttyc': bttyc,
                'qcyc': qcyc,
                'yzyc': yzyc,
            }
            bmi.wirte_f4(data4)
            self.fashe(f"{banji}数据已写入附录四")
            workbook.close()

    # 数据汇总 已开发完毕
    def Sum_result(self):
        """
        根据上每个班级的计算数据进行追加全校数据汇总
        """
        workbook = openpyxl.load_workbook("./report/附件一学生健康体检基本情况汇总.xlsx")
        self.fashe(f"附件一学生健康体检基本情况汇总.xlsx")
        sheet = workbook['Sheet1']
        max_row = sheet.max_row
        self.fashe(f"附件一最大行数为：{max_row}")
        Sum_result1 = max_row + 1
        self.fashe(f"附件一汇总行数为：{Sum_result1}")
        sheet[f'A{Sum_result1}'].value = '数据汇总'
        sheet[f'B{Sum_result1}'].value = bmi.Sumhe(sheet[f"B"][3:max_row])
        sheet[f'C{Sum_result1}'].value = bmi.Sumhe(sheet[f"C"][3:max_row])
        sheet[f'D{Sum_result1}'].value = bmi.Sumhe(sheet[f"D"][3:max_row])
        sheet[f'E{Sum_result1}'].value = bmi.baifenbi(sheet[f'D{Sum_result1}'].value, sheet[f'B{Sum_result1}'].value)

        workbook.save("./report/附件一学生健康体检基本情况汇总.xlsx")
        workbook.close()
        self.fashe(f"附件一数据汇总追加完毕")

        workbook = openpyxl.load_workbook("./report/附件二常见疾病及缺点检出情况一.xlsx")
        self.fashe(f"读取附件二常见疾病及缺点检出情况一.xlsx")
        sheet = workbook['Sheet1']
        max_row = sheet.max_row
        self.fashe(f"附件二最大行数为：{max_row}")
        Sum_result1 = max_row + 1
        self.fashe(f"附件二汇总行数为：{Sum_result1}")
        sheet[f'A{Sum_result1}'].value = '数据汇总'
        sheet[f'B{Sum_result1}'].value = bmi.Sumhe(sheet[f"B"][1:max_row])
        sheet[f'C{Sum_result1}'].value = bmi.Sumhe(sheet[f"C"][1:max_row])
        sheet[f'D{Sum_result1}'].value = bmi.baifenbi(sheet[f'C{Sum_result1}'].value, sheet[f'B{Sum_result1}'].value)
        sheet[f'E{Sum_result1}'].value = bmi.Sumhe(sheet[f"E"][1:max_row])
        sheet[f'F{Sum_result1}'].value = bmi.baifenbi(sheet[f'E{Sum_result1}'].value, sheet[f'B{Sum_result1}'].value)
        sheet[f'G{Sum_result1}'].value = bmi.Sumhe(sheet[f"G"][1:max_row])
        sheet[f'H{Sum_result1}'].value = bmi.baifenbi(sheet[f'G{Sum_result1}'].value, sheet[f'B{Sum_result1}'].value)
        sheet[f'I{Sum_result1}'].value = bmi.Sumhe(sheet[f"I"][1:max_row])
        sheet[f'J{Sum_result1}'].value = bmi.baifenbi(sheet[f'I{Sum_result1}'].value, sheet[f'B{Sum_result1}'].value)
        sheet[f'K{Sum_result1}'].value = bmi.Sumhe(sheet[f"K"][1:max_row])
        sheet[f'L{Sum_result1}'].value = bmi.baifenbi(sheet[f'K{Sum_result1}'].value, sheet[f'B{Sum_result1}'].value)
        sheet[f'M{Sum_result1}'].value = bmi.Sumhe(sheet[f"M"][1:max_row])
        sheet[f'N{Sum_result1}'].value = bmi.baifenbi(sheet[f'M{Sum_result1}'].value, sheet[f'B{Sum_result1}'].value)
        sheet[f'O{Sum_result1}'].value = bmi.Sumhe(sheet[f"O"][1:max_row])
        sheet[f'P{Sum_result1}'].value = bmi.baifenbi(sheet[f'O{Sum_result1}'].value, sheet[f'B{Sum_result1}'].value)
        sheet[f'Q{Sum_result1}'].value = bmi.Sumhe(sheet[f"Q"][1:max_row])
        sheet[f'R{Sum_result1}'].value = bmi.baifenbi(sheet[f'Q{Sum_result1}'].value, sheet[f'B{Sum_result1}'].value)
        sheet[f'S{Sum_result1}'].value = bmi.Sumhe(sheet[f"S"][1:max_row])
        sheet[f'T{Sum_result1}'].value = bmi.baifenbi(sheet[f'S{Sum_result1}'].value, sheet[f'B{Sum_result1}'].value)
        sheet[f'U{Sum_result1}'].value = bmi.Sumhe(sheet[f"U"][1:max_row])
        sheet[f'V{Sum_result1}'].value = bmi.baifenbi(sheet[f'U{Sum_result1}'].value, sheet[f'B{Sum_result1}'].value)
        workbook.save("./report/附件二常见疾病及缺点检出情况一.xlsx")
        workbook.close()
        self.fashe(f"附件二数据汇总追加完毕")
        #
        workbook = openpyxl.load_workbook("./report/附件三常见疾病及缺点检出情况二.xlsx")
        self.fashe(f"附件三常见疾病及缺点检出情况二.xlsx")
        sheet = workbook['Sheet1']
        max_row = sheet.max_row
        self.fashe(f"附件三最大行数为：{max_row}")
        Sum_result1 = max_row + 1
        self.fashe(f"附件三汇总行数为：{Sum_result1}")
        sheet[f'A{Sum_result1}'].value = '数据汇总'
        sheet[f'B{Sum_result1}'].value = bmi.Sumhe(sheet[f"B"][1:max_row])
        sheet[f'C{Sum_result1}'].value = bmi.Sumhe(sheet[f"C"][1:max_row])
        sheet[f'D{Sum_result1}'].value = bmi.baifenbi(sheet[f'C{Sum_result1}'].value, sheet[f'B{Sum_result1}'].value)
        sheet[f'E{Sum_result1}'].value = bmi.Sumhe(sheet[f"E"][1:max_row])
        sheet[f'F{Sum_result1}'].value = bmi.baifenbi(sheet[f'E{Sum_result1}'].value, sheet[f'B{Sum_result1}'].value)
        sheet[f'G{Sum_result1}'].value = bmi.Sumhe(sheet[f"G"][1:max_row])
        sheet[f'H{Sum_result1}'].value = bmi.baifenbi(sheet[f'G{Sum_result1}'].value, sheet[f'B{Sum_result1}'].value)
        sheet[f'I{Sum_result1}'].value = bmi.Sumhe(sheet[f"I"][1:max_row])
        sheet[f'J{Sum_result1}'].value = bmi.baifenbi(sheet[f'I{Sum_result1}'].value, sheet[f'B{Sum_result1}'].value)
        sheet[f'K{Sum_result1}'].value = bmi.Sumhe(sheet[f"K"][1:max_row])
        sheet[f'L{Sum_result1}'].value = bmi.baifenbi(sheet[f'K{Sum_result1}'].value, sheet[f'B{Sum_result1}'].value)
        sheet[f'M{Sum_result1}'].value = bmi.Sumhe(sheet[f"M"][1:max_row])
        sheet[f'N{Sum_result1}'].value = bmi.baifenbi(sheet[f'M{Sum_result1}'].value, sheet[f'B{Sum_result1}'].value)
        sheet[f'O{Sum_result1}'].value = bmi.Sumhe(sheet[f"O"][1:max_row])
        sheet[f'P{Sum_result1}'].value = bmi.baifenbi(sheet[f'O{Sum_result1}'].value, sheet[f'B{Sum_result1}'].value)
        sheet[f'Q{Sum_result1}'].value = bmi.Sumhe(sheet[f"Q"][1:max_row])
        sheet[f'R{Sum_result1}'].value = bmi.baifenbi(sheet[f'Q{Sum_result1}'].value, sheet[f'B{Sum_result1}'].value)
        sheet[f'S{Sum_result1}'].value = bmi.Sumhe(sheet[f"S"][1:max_row])
        sheet[f'T{Sum_result1}'].value = bmi.baifenbi(sheet[f'S{Sum_result1}'].value, sheet[f'B{Sum_result1}'].value)
        workbook.save("./report/附件三常见疾病及缺点检出情况二.xlsx")
        workbook.close()
        self.fashe(f"附件三数据汇总追加完毕")
        #
        workbook = openpyxl.load_workbook("./report/附件四学生健康体检数据汇总.xlsx")
        self.fashe(f"读取附件四学生健康体检数据汇总.xlsx")
        sheet = workbook['Sheet1']
        max_row = sheet.max_row
        self.fashe(f"附件4最大行数为：{max_row}")
        Sum_result1 = max_row + 1
        self.fashe(f"附件4汇总行数为：{Sum_result1}")
        sheet[f'A{Sum_result1}'].value = '数据汇总'
        sheet[f'B{Sum_result1}'].value = bmi.Sumhe(sheet[f"B"][1:max_row])
        sheet[f'C{Sum_result1}'].value = bmi.Sumhe(sheet[f"C"][1:max_row])
        sheet[f'D{Sum_result1}'].value = bmi.Sumhe(sheet[f"D"][1:max_row])
        sheet[f'E{Sum_result1}'].value = bmi.Sumhe(sheet[f"E"][1:max_row])
        sheet[f'F{Sum_result1}'].value = bmi.Sumhe(sheet[f"F"][1:max_row])
        sheet[f'G{Sum_result1}'].value = bmi.Sumhe(sheet[f"G"][1:max_row])
        sheet[f'H{Sum_result1}'].value = bmi.Sumhe(sheet[f"H"][1:max_row])
        sheet[f'I{Sum_result1}'].value = bmi.Sumhe(sheet[f"I"][1:max_row])
        sheet[f'J{Sum_result1}'].value = bmi.Sumhe(sheet[f"J"][1:max_row])
        sheet[f'K{Sum_result1}'].value = bmi.Sumhe(sheet[f"K"][1:max_row])
        sheet[f'L{Sum_result1}'].value = bmi.Sumhe(sheet[f"L"][1:max_row])
        sheet[f'M{Sum_result1}'].value = bmi.Sumhe(sheet[f"M"][1:max_row])
        sheet[f'N{Sum_result1}'].value = bmi.Sumhe(sheet[f"N"][1:max_row])
        sheet[f'O{Sum_result1}'].value = bmi.Sumhe(sheet[f"O"][1:max_row])
        sheet[f'P{Sum_result1}'].value = bmi.Sumhe(sheet[f"P"][1:max_row])
        sheet[f'Q{Sum_result1}'].value = bmi.Sumhe(sheet[f"Q"][1:max_row])
        sheet[f'R{Sum_result1}'].value = bmi.Sumhe(sheet[f"R"][1:max_row])
        sheet[f'S{Sum_result1}'].value = bmi.Sumhe(sheet[f"S"][1:max_row])
        sheet[f'T{Sum_result1}'].value = bmi.Sumhe(sheet[f"T"][1:max_row])
        sheet[f'U{Sum_result1}'].value = bmi.Sumhe(sheet[f"U"][1:max_row])
        sheet[f'V{Sum_result1}'].value = bmi.Sumhe(sheet[f"V"][1:max_row])
        sheet[f'W{Sum_result1}'].value = bmi.Sumhe(sheet[f"W"][1:max_row])
        sheet[f'X{Sum_result1}'].value = bmi.Sumhe(sheet[f"X"][1:max_row])
        sheet[f'Y{Sum_result1}'].value = bmi.Sumhe(sheet[f"Y"][1:max_row])
        sheet[f'Z{Sum_result1}'].value = bmi.Sumhe(sheet[f"Z"][1:max_row])
        sheet[f'AA{Sum_result1}'].value = bmi.Sumhe(sheet[f"AA"][1:max_row])
        sheet[f'AB{Sum_result1}'].value = bmi.Sumhe(sheet[f"AB"][1:max_row])
        sheet[f'AC{Sum_result1}'].value = bmi.Sumhe(sheet[f"AC"][1:max_row])
        sheet[f'AD{Sum_result1}'].value = bmi.Sumhe(sheet[f"AD"][1:max_row])
        sheet[f'AE{Sum_result1}'].value = bmi.Sumhe(sheet[f"AE"][1:max_row])
        sheet[f'AF{Sum_result1}'].value = bmi.Sumhe(sheet[f"AF"][1:max_row])
        workbook.save("./report/附件四学生健康体检数据汇总.xlsx")
        workbook.close()
        self.fashe(f"附件4数据汇总追加完毕")

    # 生成报告 已开发完毕
    def Baogao(self):
        """
        自动生成报告
        """
        workbook = openpyxl.load_workbook("./report/附件四学生健康体检数据汇总.xlsx")
        sheet = workbook['Sheet1']
        # 获取最大行 用以读取最后一行汇总数据 写入报告
        max = sheet.max_row
        # 创建一个文档
        document = Document()
        # 设置正文字体类型、大小
        document.styles["Normal"].font.name = u'仿宋'
        document.styles["Normal"].font.size = Pt(16)
        document.styles["Normal"]._element.rPr.rFonts.set(qn('w:eastAsia'), u'仿宋')

        for sec in document.sections:
            # 设置页面边距(左上25毫米，右下15毫米)
            sec.top_margin = Cm(2.5)
            sec.left_margin = Cm(2.5)
            sec.right_margin = Cm(1.5)
            sec.bottom_margin = Cm(1.5)
            # 设置纸张大小(A4)
            sec.page_height = Mm(297)
            sec.page_width = Mm(210)
            # 设置页眉页脚距离
            sec.header_distance = Cm(1.5)
            sec.footer_distance = Cm(0.2)

        # 首页
        paragraph9 = document.add_paragraph()
        paragraph9 = document.add_paragraph()
        paragraph9 = document.add_paragraph()
        paragraph9 = document.add_paragraph()
        paragraph = document.add_paragraph()
        run = paragraph.add_run(f'{self.lin1.text()}\n学生健康体检结果分析报告')
        run.font.name = u'仿宋'
        run.font.size = Pt(32)
        run.bold = True
        # 标题居中对齐
        paragraph.alignment = WD_PARAGRAPH_ALIGNMENT.CENTER
        # 标题加粗
        paragraph.bold = True
        paragraph1 = document.add_paragraph()
        paragraph1 = document.add_paragraph()
        paragraph1 = document.add_paragraph()
        paragraph1 = document.add_paragraph()
        paragraph1 = document.add_paragraph()
        paragraph2 = document.add_paragraph(f'受检单位：{self.lin1.text()}')
        paragraph2.bold = True
        paragraph2.alignment = WD_PARAGRAPH_ALIGNMENT.CENTER
        paragraph3 = document.add_paragraph(f'体检机构：{self.lin2.text()}')
        paragraph3.bold = True
        paragraph3.alignment = WD_PARAGRAPH_ALIGNMENT.CENTER
        paragraph4 = document.add_paragraph(f'健康咨询电话：{self.lin3.text()}')
        paragraph4.bold = True
        paragraph4.alignment = WD_PARAGRAPH_ALIGNMENT.CENTER
        paragraph5 = document.add_paragraph(f'体检日期：{self.lin4.text()}')
        paragraph5.bold = True
        paragraph5.alignment = WD_PARAGRAPH_ALIGNMENT.CENTER
        document.add_page_break()
        biaoti = document.add_paragraph('学生健康体检结果分析报告')
        biaoti.bold = True
        biaoti.alignment = WD_PARAGRAPH_ALIGNMENT.CENTER
        zhengwen = document.add_paragraph(f'{self.lin1.text()}:')
        zhengwen.alignment = WD_PARAGRAPH_ALIGNMENT.LEFT
        zhengwen1 = document.add_paragraph(
            f'    根据四川省卫生厅、四川省教育厅关于印发《四川省中小学生健康体检管理办法》的通知（川卫办发〔2009〕424号）和《绵阳市游仙区教育和体育局办公室＜关于开展2020年中小学生健康体检工作的通知＞》('
            f'绵游教体办〔2020〕8号)文件要求，我中心于{self.lin4.text()}对贵校在校学生开展了健康体检，现将学生健康体检结果及存在的主要健康问题分析报告如下：')
        zhengwen1 = document.add_paragraph('    一、健康体检内容')
        zhengwen1 = document.add_paragraph('    1.既往病史询问。')
        zhengwen1 = document.add_paragraph('    2.体检项目：')
        zhengwen1 = document.add_paragraph('    2.1内科常规检查:心脏、肺部、肝脏、脾脏;')
        zhengwen1 = document.add_paragraph('    2.2外科检查:头部、颈部、胸部、脊柱、四肢、皮肤、淋巴结;')
        zhengwen1 = document.add_paragraph('    2.3五官科检查:裸眼（矫正）视力、沙眼、结膜炎、耳、鼻、喉；')
        zhengwen1 = document.add_paragraph('    2.4口腔科检查:龋齿、牙周组织;')
        zhengwen1 = document.add_paragraph('    2.5形体指标检查:身高、体重。')
        zhengwen1 = document.add_paragraph(
            '    其他项目均根据国家相关法律、法规规定所要求开展的检查项目或根据具体情况,进行适当增补。')
        zhengwen1 = document.add_paragraph('    二、结果分析意义')
        zhengwen1 = document.add_paragraph(
            '学龄儿童青少年时期是人体发育的重要时期，其身体素质状况决定了后期身体健康状况，事关中华民族的长远发展，是衡量国家综合国力的重要指标之一。为此，我中心抽调各科骨干组成体检组，严格按照《学生健康检查技术规范GB'
            '/T '
            '26343-2010'
            '》及其他相关健康体检管理规定，对参检学生进行了认真的检查，由分析小组对学生体检情况及各项数据进行了科学分析，旨在通过数据分析，全面掌握学生各形态和机能指标的增长规律、特点和变化趋势，分析其生长发育水平、评价学生现时营养状况及掌握疾病的发生、发展规律和现状，为学校体育卫生工作健康、有序地开展提供科学依据。')
        zhengwen1 = document.add_paragraph('    三、结果分析方法')
        zhengwen1 = document.add_paragraph(
            '我中心抽调临床经验丰富的医生组成体检结果分析小组，严格按照《学生健康检查技术规范GB/T 26343-2010》《学龄儿童青少年营养不良筛查WS/T '
            '456-2014》《7岁-18岁儿童青少年身高发育等级评价WS/T 612-2018》《学龄儿童青少年超重与肥胖筛查WS/T 586-2018》《7岁-18岁儿童青少年血压偏高筛查界值WS/T '
            '610-2018》《儿童青少年发育水平的综合评价GB/T '
            '31178-2014》等国家和卫生行业标准开展分析。通过统计体检学生各形态、机能指标的均数系列指标（均数、标准差等）和各常见疾病患病的检出情况，综合评价受检对象身高、体重、生长发育水平和现时身体健康状况，并提出健康指导意见。')
        zhengwen1 = document.add_paragraph('    四、结果分析内容')
        zhengwen1 = document.add_paragraph('   （一）学生参检情况')
        zhengwen2 = document.add_paragraph(
            f"   本次健康体检，贵校初中部{len(sheet[f'B'][1:max - 1])}个班级应参加体检人数{sheet[f'B{max}'].value}名，实际参加体检{sheet[f'C{max}'].value}人，参检率{bmi.baifenbi(sheet[f'C{max}'].value, sheet[f'B{max}'].value)}，具体参检人数明细详见附件1。")
        zhengwen2 = document.add_paragraph('   （二）形态、机能发育指标分析')
        zhengwen2 = document.add_paragraph(
            '    通过对体检数据的汇总分析：贵校各年龄段学生形态、机能指标的发育符合学生生长发育的一般规律。身高、体重随年龄增长而增长。其中身高和体重出现明显增长趋势，显示出学生的体形出现向超重及肥胖发展的趋势，这与目前生活水平提高、生活节奏加快、生活方式改变，摄入过多高能量食物，对身体锻炼重视程度不够，缺乏有效锻炼均有直接关系，与肥胖和超重在学生中发生率逐年提高的趋势契合。')
        zhengwen2 = document.add_paragraph("   （三）生长发育水平及营养状况分析")
        zhengwen2 = document.add_paragraph(
            f"   通过对体检数据的汇总分析：贵校学生营养健康状况较好。男女生的正常体重和身高比率符合相关标准。学生实际体测人数为{sheet[f'C{max}'].value}人，体重偏轻男生{sheet[f'G{max}'].value}人，占总人数{bmi.baifenbi(sheet[f'G{max}'].value, sheet[f'C{max}'].value)}，女生{sheet[f'K{max}'].value}人，占总人数{bmi.baifenbi(sheet[f'K{max}'].value, sheet[f'C{max}'].value)}；体重超重男生{sheet[f'H{max}'].value}人，占总人数{bmi.baifenbi(sheet[f'H{max}'].value, sheet[f'C{max}'].value)}，女生{sheet[f'L{max}'].value}人，占总人数{bmi.baifenbi(sheet[f'L{max}'].value, sheet[f'C{max}'].value)}；肥胖男生{sheet[f'I{max}'].value}人，占总人数{bmi.baifenbi(sheet[f'I{max}'].value, sheet[f'C{max}'].value)}，女生{sheet[f'M{max}'].value}人，占总人数{bmi.baifenbi(sheet[f'M{max}'].value, sheet[f'C{max}'].value)}。男性生长迟缓{sheet[f'J{max}'].value}人，占总人数{bmi.baifenbi(sheet[f'J{max}'].value, sheet[f'C{max}'].value)}。女性生长迟缓{sheet[f'N{max}'].value}人，占总人数{bmi.baifenbi(sheet[f'N{max}'].value, sheet[f'C{max}'].value)}。其中肥胖需予以重视，儿童青少年时期是饮食行为建立和发展的关键时期，良好的饮食行为不仅保证了儿童良好的营养状态，而且会持续至成人对成人饮食行为的建立和健康产生深刻的影响。儿童青少年肥胖危害深远，不仅导致身心疾患和生理功能障碍，影响学习能力，更为严重的是儿童青少年肥胖可发展为成人肥胖，引起高血压、心脏病、糖尿病等慢性疾病，从而导致长期病态和早期死亡。")
        zhengwen3 = document.add_paragraph('   （四）常见疾病检出情况分析')
        zhengwen4 = document.add_paragraph(
            f"   从内科和外科检查情况来看，心脏异常人数{sheet[f'R{max}'].value}例，占比{bmi.baifenbi(sheet[f'R{max}'].value, sheet[f'C{max}'].value)}；肺部异常人数{sheet[f'S{max}'].value}例，占比{bmi.baifenbi(sheet[f'S{max}'].value, sheet[f'C{max}'].value)}；肝脏脾脏异常人数{sheet[f'T{max}'].value}例，占比{bmi.baifenbi(sheet[f'T{max}'].value, sheet[f'C{max}'].value)}；头部异常人数{sheet[f'U{max}'].value}例，占比{bmi.baifenbi(sheet[f'U{max}'].value, sheet[f'C{max}'].value)}；颈部异常人数{sheet[f'V{max}'].value}例，占比{bmi.baifenbi(sheet[f'V{max}'].value, sheet[f'C{max}'].value)}；胸部异常人数{sheet[f'W{max}'].value}例，占比{bmi.baifenbi(sheet[f'W{max}'].value, sheet[f'C{max}'].value)}；脊柱部位异常人数{sheet[f'X{max}'].value}例，占比{bmi.baifenbi(sheet[f'X{max}'].value, sheet[f'C{max}'].value)}；四肢异常人数{sheet[f'Y{max}'].value}例，占比{bmi.baifenbi(sheet[f'Y{max}'].value, sheet[f'C{max}'].value)}；皮肤异常人数{sheet[f'Z{max}'].value}例，占比{bmi.baifenbi(sheet[f'Z{max}'].value, sheet[f'C{max}'].value)}；淋巴结异常人数{sheet[f'AA{max}'].value}例，占比{bmi.baifenbi(sheet[f'AA{max}'].value, sheet[f'C{max}'].value)}；等项目（详见附件2）。从五官科、口腔科检查情况来看，耳异常人数{sheet[f'AB{max}'].value}例，占比{bmi.baifenbi(sheet[f'AB{max}'].value, sheet[f'C{max}'].value)}，鼻异常{sheet[f'AC{max}'].value}例，占比{bmi.baifenbi(sheet[f'AC{max}'].value, sheet[f'C{max}'].value)}。喉异常{sheet[f'AD{max}'].value}例，占比{bmi.baifenbi(sheet[f'AD{max}'].value, sheet[f'C{max}'].value)}。牙周疾病{sheet[f'AF{max}'].value}例，占比{bmi.baifenbi(sheet[f'AF{max}'].value, sheet[f'C{max}'].value)}。从眼科体检情况来看，轻度视力不良{sheet[f'O{max}'].value}例，占比{bmi.baifenbi(sheet[f'O{max}'].value, sheet[f'C{max}'].value)}；中度视力不良{sheet[f'P{max}'].value}例，占比{bmi.baifenbi(sheet[f'P{max}'].value, sheet[f'C{max}'].value)}；重度视力不良{sheet[f'Q{max}'].value}例，占比{bmi.baifenbi(sheet[f'Q{max}'].value, sheet[f'C{max}'].value)}；龋齿及龋坏人数{sheet[f'AE{max}'].value}人，占比{bmi.baifenbi(sheet[f'AE{max}'].value, sheet[f'C{max}'].value)}（详见附件3）。")
        zhengwen1 = document.add_paragraph('    五、健康指导意见')
        zhengwen1 = document.add_paragraph(
            '    （一）沙眼:沙眼是由沙眼衣原体引起的一种慢性传染性眼病。感染原因主要是由于不良的卫生习惯引起病原体的传播。因学生群体是沙眼的好发人群，并易反复感染，严重可致盲，学校应在平时注意加强卫生宣传教育，向学生普及沙眼防治知识。要求学生培养良好的卫生习惯。教育学生养成勤洗手、勤剪指甲、不用手擦眼睛，每天用餐巾纸或清洁的手帕擦眼睛。提倡一人一条毛巾，最好用流水洗脸、用除菌香皂或洗手液洗手，避免交叉感染。定期检查，发现问题及时告知家长，积极进行治疗。')
        zhengwen1 = document.add_paragraph(
            '    （三）龋齿:龋病是一种非自愈性疾病，是影响儿童青少年健康和生长发育最常见的口腔疾病之一。龋齿的形成与甜食和碳酸饮料进食过多、睡前不刷牙或刷牙方法不正确，以及牙齿自身本质情况有关。因此，学校要认真开展口腔卫生宣传教育，提高学生口腔健康意识，让学生了解龋病的危害及预防等口腔保健知识，培养学生养成良好的口腔卫生习惯。此外，要引导学生选用合适的牙刷及含氟牙膏，掌握正确的刷牙方法，做到饭后漱口，早晚刷牙。在饮食摄入方面，要求学生要尽量少食甜食、零食，多吃蔬菜水果。对已查出患有龋齿或龋患的学生，应通过填充等方式及早治疗。')
        zhengwen1 = document.add_paragraph(
            '    （四）肥胖:引起肥胖的主要原因是缺乏合理营养知识，热量摄取过多，尤其是甜食摄入过多，加之课业负担过重，体育锻炼时间过少，降低了热量的消耗。学校应通过对学生营养餐的不断改进，加强对学生营养知识的教育，改变其不良饮食习惯，加强校内外的体育锻炼，以降低超重和肥胖的发生。同时，要引导学生早餐以稀饭、馒头、包子为主；午餐荤素搭配，多吃蔬菜、水果，腌、辣、油炸、红烧等食物不宜多吃或尽量少吃；晚餐宜清淡。对患肥胖症的学生要进行膳食指导，鼓励其参加体育锻炼。')
        zhengwen1 = document.add_paragraph(
            '    （五）营养不良:学生营养不良与饮食习惯、缺少锻炼有较大关系。个别女生为了保持身材，刻意少吃，甚至出现厌食情绪，导致营养不良。一方面要普及营养知识，进行营养健康教育，另一方面，学校和老师应鼓励学生多参加体育锻炼。')
        zhengwen1 = document.add_paragraph(
            '    （六）扁桃体肿大(扁桃体炎):扁桃体肿大是一种咽扁桃体不正常肿大，分为急性扁桃体炎及慢性扁桃体炎，扁桃体炎发病原因主要是人体因劳累、受凉或其他原因而使抵抗力减弱，病原体迅速繁殖而引起，应尽早正规治疗。扁桃体一般3～10岁时最大，10岁以后逐渐萎缩，因此儿童时期的扁桃体炎是防治的重点。首先在于增强机体的抵抗力，加强锻炼，特别是冬季，要多参与户外活动，使身体对寒冷的适应能力增强，减少扁桃体发炎的机会，并注意劳逸结合，气温变化明显时注意保暖；保持口腔清洁，吃东西后要漱口；搞好环境卫生，室内应光线充足，空气流通；保持适宜的温度和湿度。对急性扁桃体炎的学生应及时就诊，合理休息。')
        zhengwen1 = document.add_paragraph(
            '    （七）甲状腺肿大:甲状腺肿大是以缺碘为主的代偿性甲状腺肿大，青年女性多见，一般不伴有甲状腺功能异常，散发性甲状腺肿大可有多种病因导致相似结果，即机体对甲状腺激素需求增加，或甲状腺激素生成障碍。学校应敦促学生家长定期带孩子进行健康体检，发现异常者需及时查清病因、对症治疗。')
        zhengwen1 = document.add_paragraph('    （八）其他检出的疾病和阳性体征及健康指导意见：')
        zhengwen1 = document.add_paragraph(
            '      1.脊柱弯曲：脊柱弯曲是学生中一种较常见的疾病。一般以侧弯最为多见，其次是脊柱前后轴弯曲，通过矫治可以恢复正常。对脊柱弯曲的预防应加强对学生的姿势教育，尤其对小学阶段的学生，要督促其养成良好的读写习惯，注意端正身体姿势，行走、读写时胸部自然挺直，两肩齐平向后扩展，站立时重心在两脚上，提倡用双肩背书包。学校与家庭应配备适合学生身高的课桌椅，鼓励学生多参加体育活动，认真做好早操和课间操。定期检查，早期发现，及时矫正。对体检学生脊柱异常者，要根据脊柱弯曲的方向及部位进行矫正操治疗；对于脊柱Ⅱ度以上弯曲者，应到医院进一步诊治。')
        zhengwen1 = document.add_paragraph(
            '      2.扁平足：扁平足青少年学生中常见的姿势缺陷之一。它影响走路的能力和身体的姿势。学生自身无不适感，只是在检查中发现，属于轻度，易于矫治。学校平时应鼓励学生参加体育活动，加强足部肌肉及韧带力量。')
        zhengwen1 = document.add_paragraph(
            '      3.心、肺、肝、脾等重要脏器：心脏杂音分为生理性杂音和病理性杂音，临床上青少年和儿童多见于生理性杂音，杂音性质柔和、强度在2/6级以下；病理性杂音见于心脏器质性病变等。心律失常（窦性心动过速、窦性心动过缓、偶发早搏、心律不齐等）在平时体检中比较常见，在排除器质性病变后，无需特殊治疗。建议：对心脏听诊有杂音疑问和心律失常的学生要告知其家长，应进一步到综合医院心内科就诊，完善心脏彩超和心电图等检查，排除是否存在器质性心脏疾病，以便得到及时诊断和治疗。检查并确诊为心血管系统疾病的学生应定期诊治，避免参加剧烈运动，预防感冒。')
        zhengwen1 = document.add_paragraph(
            '      综合上述学生体检结果，贵校学生的视力低下情况较显著，龋齿比例高，部分学生存在超重和肥胖现象，学生营养状况仍需要持续关注。学生健康应引起家长、学校及社会各界的重视。')
        zhengwen1 = document.add_paragraph('       附件：1.附件一学生健康体检基本情况汇总表')
        zhengwen1 = document.add_paragraph('       附件：2.附件二常见疾病及缺点检出情况表一')
        zhengwen1 = document.add_paragraph('       附件：3.附件三常见疾病及缺点检出情况表二')
        zhengwen1 = document.add_paragraph('       附件：4.附件四学生健康体检数据汇总')
        zhengwen1 = document.add_paragraph(f'       附件：5.{self.lin1.text()}学校体检班级明细表')
        zhengwen1 = document.add_paragraph()
        zhengwen1 = document.add_paragraph()
        zhengwen1 = document.add_paragraph(f"                        {self.lin2.text()}")
        zhengwen1 = document.add_paragraph(f"                                   {datetime.date.today()}")
        # 保存文档
        document.save(f'./report/{self.fileName}学生健康体检结果分析报告.docx')
        time.sleep(2)
        workbook.close()
        return self.fashe("报告生成完毕。可以送总检了")

    # 数据维度分离 已开发完毕
    def data_fl(self):
        align = Alignment(horizontal="center", vertical="center", wrap_text=True)
        font = Font(name="宋体", size=9)
        work = openpyxl.Workbook()
        workbook = openpyxl.load_workbook(f'./report/{self.fileName}体检数据明细表.xlsx')
        books = workbook.sheetnames

        for page in books:
            for row in workbook[f'{page}'].iter_rows(min_row=5, max_row=workbook[f'{page}'].max_row, values_only=True):
                if row[4] is None or row[5] is None:
                    pass
                else:
                    sheet_names = work.sheetnames
                    sheetname = str(row[4]) + '性' + str(row[5])
                    if sheetname in sheet_names:
                        work[f"{sheetname}"].append(row)
                    else:
                        sheet = work.create_sheet(f'{sheetname}')
                        sheet['B1'] = '学年'
                        sheet['B1'].alignment = align

                        sheet['C1'] = '第一学年'
                        sheet['C1'].alignment = align

                        sheet['A2'] = '序号'
                        sheet['A2'].alignment = align
                        sheet.merge_cells('A2:A4')

                        sheet['B2'] = '年级班级'
                        sheet.merge_cells('B2:B4')
                        sheet['B2'].alignment = align

                        sheet['C2'] = '学生姓名'
                        sheet.merge_cells('C2:C4')
                        sheet['C2'].alignment = align

                        sheet['D2'] = '身份证号码'
                        sheet.merge_cells('D2:D4')
                        sheet['D2'].alignment = align

                        sheet['E2'] = '性别'
                        sheet.merge_cells('E2:E4')
                        sheet['E2'].alignment = align

                        sheet['F2'] = '年龄'
                        sheet.merge_cells('F2:F4')
                        sheet['F2'].alignment = align

                        sheet['G2'] = '到检情况'
                        sheet.merge_cells('G2:G4')
                        sheet['G2'].alignment = align

                        sheet['H2'] = '学籍号'
                        sheet.merge_cells('H2:H4')
                        sheet['H2'].alignment = align

                        sheet['I2'] = '查期'
                        sheet.merge_cells('I2:I4')
                        sheet['I2'].alignment = align

                        sheet['J2'] = '发育及营养状况'
                        sheet.merge_cells('J2:J4')
                        sheet['J2'].alignment = align

                        sheet['K2'] = '身高(cm)'
                        sheet.merge_cells('K2:K4')
                        sheet['K2'].alignment = align

                        sheet['L2'] = '体重(kg)'
                        sheet.merge_cells('L2:L4')
                        sheet['L2'].alignment = align

                        sheet['M2'] = '胸围(cm)'
                        sheet.merge_cells('M2:M4')
                        sheet['M2'].alignment = align

                        sheet['N2'] = '肺活量(ml)'
                        sheet.merge_cells('N2:N4')
                        sheet['N2'].alignment = align

                        sheet['O2'] = '血压(mmhg)'
                        sheet.merge_cells('O2:O4')
                        sheet['O2'].alignment = align

                        sheet['P2'] = '脉搏(次/分)'
                        sheet.merge_cells('P2:P4')
                        sheet['P2'].alignment = align

                        sheet['Q2'] = '视力'
                        sheet.merge_cells('Q2:V2')
                        sheet['Q2'].alignment = align

                        sheet['Q3'] = '左'
                        sheet.merge_cells('Q3:S3')
                        sheet['Q3'].alignment = align

                        sheet['T3'] = '右'
                        sheet.merge_cells('T3:V3')
                        sheet['T3'].alignment = align

                        sheet['Q4'] = '裸眼视力'
                        sheet['Q4'].alignment = align

                        sheet['R4'] = '矫正视力'
                        sheet['R4'].alignment = align

                        sheet['S4'] = '矫正度数'
                        sheet['S4'].alignment = align

                        sheet['T4'] = '裸眼视力'
                        sheet['T4'].alignment = align

                        sheet['U4'] = '矫正视力'
                        sheet['U4'].alignment = align

                        sheet['V4'] = '矫正度数'
                        sheet['V4'].alignment = align

                        sheet['W2'] = '听力'
                        sheet.merge_cells('W2:X2')
                        sheet['W2'].alignment = align

                        sheet['W3'] = '左'
                        sheet['W3'].alignment = align

                        sheet['X3'] = '右'
                        sheet['X3'].alignment = align

                        sheet['Y2'] = '沙眼'
                        sheet.merge_cells('Y2:Y4')
                        sheet['Y2'].alignment = align

                        sheet['Z2'] = '色觉'
                        sheet.merge_cells('Z2:Z4')
                        sheet['Z2'].alignment = align

                        sheet['AA2'] = '鼻病'
                        sheet.merge_cells('AA2:AA4')
                        sheet['AA2'].alignment = align

                        sheet['AB2'] = '口腔'
                        sheet.merge_cells('AB2:AB4')
                        sheet['AB2'].alignment = align

                        sheet['AC2'] = '心脏'
                        sheet.merge_cells('AC2:AC4')
                        sheet['AC2'].alignment = align

                        sheet['AD2'] = '肺'
                        sheet.merge_cells('AD2:AD4')
                        sheet['AD2'].alignment = align

                        sheet['AE2'] = '肝脾'
                        sheet.merge_cells('AE2:AE4')
                        sheet['AE2'].alignment = align

                        sheet['AF2'] = '胃肠'
                        sheet.merge_cells('AF2:AF4')
                        sheet['AF2'].alignment = align

                        sheet['AG2'] = '脊柱'
                        sheet.merge_cells('AG2:AG4')
                        sheet['AG2'].alignment = align

                        sheet['AH2'] = '四肢'
                        sheet.merge_cells('AH2:AH4')
                        sheet['AH2'].alignment = align

                        sheet['AI2'] = '平足'
                        sheet.merge_cells('AI2:AI4')
                        sheet['AI2'].alignment = align

                        sheet['AJ2'] = '皮肤'
                        sheet.merge_cells('AJ2:AJ4')
                        sheet['AJ2'].alignment = align

                        sheet['AK2'] = '传染病'
                        sheet.merge_cells('AK2:AK4')
                        sheet['AK2'].alignment = align

                        sheet['AL2'] = '胸透'
                        sheet.merge_cells('AL2:AL4')
                        sheet['AL2'].alignment = align

                        sheet['AM2'] = '既往病史'
                        sheet.merge_cells('AM2:AM4')
                        sheet['AM2'].alignment = align

                        sheet['AN2'] = '医院名或校医名'
                        sheet.merge_cells('AN2:AN4')
                        sheet['AN2'].alignment = align

                        sheet['AO2'] = '头部'
                        sheet.merge_cells('AO2:AO4')
                        sheet['AO2'].alignment = align

                        sheet['AP2'] = '颈部'
                        sheet.merge_cells('AP2:AP4')
                        sheet['AP2'].alignment = align

                        sheet['AQ2'] = '胸部'
                        sheet.merge_cells('AQ2:AQ4')
                        sheet['AQ2'].alignment = align

                        sheet['AR2'] = '淋巴'
                        sheet.merge_cells('AR2:AR4')
                        sheet['AR2'].alignment = align

                        sheet['AS2'] = '扁桃体'
                        sheet.merge_cells('AS2:AS4')
                        sheet['AS2'].alignment = align

                        sheet['AT2'] = 'BMI'
                        sheet.merge_cells('AT2:AT4')
                        sheet['AT2'].alignment = align

                        sheet['AU2'] = 'BMI对比结果'
                        sheet.merge_cells('AU2:AU4')
                        sheet['AU2'].alignment = align

                        sheet['AV2'] = '身高对比结果'
                        sheet.merge_cells('AV2:AV4')
                        sheet['AV2'].alignment = align

                        sheet['AW2'] = '视力对比结果'
                        sheet.merge_cells('AW2:AW4')
                        sheet['AW2'].alignment = align

                        sheet['AX2'] = '耳异常'
                        sheet.merge_cells('AX2:AX4')
                        sheet['AX2'].alignment = align

                        sheet['AY2'] = '龋齿'
                        sheet.merge_cells('AY2:AY4')
                        sheet['AY2'].alignment = align

                        sheet.row_dimensions[2].height = 19.5
                        sheet.column_dimensions['A'].width = 8.38
                        sheet.column_dimensions['B'].width = 11.88
                        sheet.column_dimensions['C'].width = 14
                        sheet.column_dimensions['D'].width = 20.13
                        sheet.column_dimensions['E'].width = 15.63
                        sheet.column_dimensions['F'].width = 7
                        sheet.column_dimensions['G'].width = 7.5
                        sheet.column_dimensions['H'].width = 7.5
                        sheet.column_dimensions['I'].width = 7.5
                        sheet.column_dimensions['J'].width = 7.5
                        sheet.column_dimensions['K'].width = 7.5
                        sheet.column_dimensions['L'].width = 11
                        sheet.column_dimensions['M'].width = 8.38
                        sheet.column_dimensions['N'].width = 8.38
                        sheet.column_dimensions['O'].width = 8.38
                        sheet.column_dimensions['P'].width = 8.38
                        sheet.column_dimensions['Q'].width = 8.38
                        sheet.column_dimensions['R'].width = 8.38
                        sheet.column_dimensions['S'].width = 8.38
                        sheet.column_dimensions['T'].width = 8.38
                        sheet.column_dimensions['U'].width = 8.38
                        sheet.column_dimensions['V'].width = 8.38
                        sheet.column_dimensions['W'].width = 8.38
                        sheet.column_dimensions['X'].width = 8.38
                        sheet.column_dimensions['Y'].width = 15.75
                        sheet.column_dimensions['Z'].width = 8.38
                        sheet.column_dimensions['AA'].width = 8.38
                        sheet.column_dimensions['AB'].width = 8.38
                        sheet.column_dimensions['AC'].width = 8.38
                        sheet.column_dimensions['AD'].width = 8.38
                        sheet.column_dimensions['AE'].width = 8.38
                        sheet.column_dimensions['AF'].width = 8.38
                        sheet.column_dimensions['AG'].width = 8.38
                        sheet.column_dimensions['AH'].width = 8.38
                        sheet.column_dimensions['AI'].width = 11.88
                        sheet.column_dimensions['AJ'].width = 11.88
                        sheet.column_dimensions['AK'].width = 11.88
                        sheet.column_dimensions['AL'].width = 11.88
                        sheet.column_dimensions['AM'].width = 11.88
                        sheet.column_dimensions['AN'].width = 11.88
                        sheet.column_dimensions['AO'].width = 11.88
                        sheet.column_dimensions['AP'].width = 11.88
                        sheet.column_dimensions['AQ'].width = 11.88
                        sheet.column_dimensions['AR'].width = 11.88
                        sheet.column_dimensions['AS'].width = 11.88
                        sheet.column_dimensions['AT'].width = 11.88
                        sheet.column_dimensions['AU'].width = 11.88
                        sheet.column_dimensions['AV'].width = 11.88
                        sheet.column_dimensions['AW'].width = 11.88
                        sheet.column_dimensions['AX'].width = 11.88
                        sheet.column_dimensions['AY'].width = 11.88
                        for row1 in sheet:
                            for cell in row1:
                                sheet[cell.coordinate].font = font
                        sheet.append(row)
        sheet1 = work['Sheet']
        work.remove(sheet1)
        work.save(f'./report/{self.fileName}数据分离明细表.xlsx')
        work.close()
        workbook.close()
        return self.fashe(f'./report/{self.fileName}数据分离完毕')

    # 数据统计
    def data_tj(self):
        workbook = openpyxl.load_workbook(f'./report/{self.fileName}数据分离明细表.xlsx')
        workbook1 = openpyxl.load_workbook(f'./学校体检模板/附件五学生健康体检数据分段汇总.xlsx')
        books = workbook.sheetnames
        for page in books:
            max = workbook1['Sheet1'].max_row
            maxrow = max + 1
            max1 = workbook[f'{page}'].max_row
            sheet = workbook1['Sheet1']
            sheet[f'A{maxrow}'].value = workbook[f'{page}']['E5'].value
            sheet[f'B{maxrow}'].value = workbook[f'{page}']['F5'].value
            sheet[f'C{maxrow}'].value = bmi.count(workbook[f'{page}']['C'])
            sheet[f'D{maxrow}'].value = bmi.count(workbook[f'{page}']['G'])
            sheet[f'E{maxrow}'].value = float(sheet[f'C{maxrow}'].value) - float(sheet[f'D{maxrow}'].value)
            sheet[f'F{maxrow}'].value = bmi.xscount(workbook[f'{page}']['AU'])['消瘦']
            sheet[f'G{maxrow}'].value = bmi.xscount(workbook[f'{page}']['AU'])['超重']
            sheet[f'H{maxrow}'].value = bmi.xscount(workbook[f'{page}']['AU'])['肥胖']
            sheet[f'I{maxrow}'].value = bmi.chcount(workbook[f'{page}']['AV'])
            sheet[f'J{maxrow}'].value = bmi.shilicount(workbook[f'{page}']['AW'])['qsl']
            sheet[f'K{maxrow}'].value = bmi.shilicount(workbook[f'{page}']['AW'])['zsl']
            sheet[f'L{maxrow}'].value = bmi.shilicount(workbook[f'{page}']['AW'])['zdsl']
            sheet[f'M{maxrow}'].value = bmi.count(workbook[f'{page}']['AC'])
            sheet[f'N{maxrow}'].value = bmi.count(workbook[f'{page}']['AD'])
            sheet[f'O{maxrow}'].value = bmi.count(workbook[f'{page}']['AE'])
            sheet[f'P{maxrow}'].value = bmi.count(workbook[f'{page}']['AO'])
            sheet[f'Q{maxrow}'].value = bmi.count(workbook[f'{page}']['AP'])
            sheet[f'R{maxrow}'].value = bmi.count(workbook[f'{page}']['AQ'])
            sheet[f'S{maxrow}'].value = bmi.count(workbook[f'{page}']['AG'])
            sheet[f'T{maxrow}'].value = bmi.count(workbook[f'{page}']['AH'])
            sheet[f'U{maxrow}'].value = bmi.count(workbook[f'{page}']['AJ'])
            sheet[f'V{maxrow}'].value = bmi.count(workbook[f'{page}']['AR'])
            sheet[f'W{maxrow}'].value = bmi.count(workbook[f'{page}']['AX'])
            sheet[f'X{maxrow}'].value = bmi.count(workbook[f'{page}']['AA'])
            sheet[f'Y{maxrow}'].value = bmi.count(workbook[f'{page}']['AS'])
            sheet[f'Z{maxrow}'].value = bmi.count(workbook[f'{page}']['AY'])
            sheet[f'AA{maxrow}'].value = bmi.count(workbook[f'{page}']['AA'])

        workbook1.save(f'./report/附件五{self.fileName}学生健康体检数据分段汇总.xlsx')
        workbook.close()
        workbook1.close()
        self.fashe('数据统计完毕！')

    # 数据写入校方要求表格
    def data_wr(self):
        workbook = openpyxl.load_workbook(f'./report/{self.fileName}体检数据明细表.xlsx')
        workbook1 = openpyxl.load_workbook(f'./学校体检模板/南山中学专版样表.xlsx')
        sheet = workbook1['学生综合素质体检信息']
        books = workbook.sheetnames
        for page in books:
            for row in workbook[f'{page}'].iter_rows(min_row=5, max_row=workbook[f'{page}'].max_row, values_only=True):
                data = {
                    '序号': row[0],
                    '年级班级': row[1],
                    '学生姓名': row[2],
                    '学籍号': row[7],
                    '查期': row[8],
                    '发育及营养状况': row[9],
                    '身高（cm）': row[10],
                    '体重（kg）': row[11],
                    '胸围（cm）': row[12],
                    '肺活量（ml）': row[13],
                    '血压（mmhg）': row[14],
                    '脉搏（次/分）': row[15],
                    '左裸眼视力': row[16],
                    '左矫正视力': row[17],
                    '左矫正度数': row[18],
                    '右裸眼视力': row[19],
                    '右矫正视力': row[20],
                    '右矫正度数': row[21],
                    '听力左': row[22],
                    '听力右': row[23],
                    '沙眼': row[24],
                    '色觉': row[25],
                    '鼻病': row[26],
                    '口腔': row[27],
                    '心脏': row[28],
                    '肺': row[29],
                    '肝脾': row[30],
                    '胃肠': row[31],
                    '脊柱': row[32],
                    '四肢': row[33],
                    '平足': row[34],
                    '皮肤': row[35],
                    '传染病': row[36],
                    '胸透': row[37],
                    '既往病史': row[38],
                    '医院名或校医名': row[39],
                    '其他1': row[40],
                    '其他2': row[41],
                    '其他3': row[42],
                    '其他4': row[43],
                    '其他5': row[44],
                }
                sheet.append([
                    data['序号'],
                    data['年级班级'],
                    data['学生姓名'],
                    data['学籍号'],
                    data['查期'],
                    data['发育及营养状况'],
                    data['身高（cm）'],
                    data['体重（kg）'],
                    data['胸围（cm）'],
                    data['肺活量（ml）'],
                    data['血压（mmhg）'],
                    data['脉搏（次/分）'],
                    data['左裸眼视力'],
                    data['左矫正视力'],
                    data['左矫正度数'],
                    data['右裸眼视力'],
                    data['右矫正视力'],
                    data['右矫正度数'],
                    data['听力左'],
                    data['听力右'],
                    data['沙眼'],
                    data['色觉'],
                    data['鼻病'],
                    data['口腔'],
                    data['心脏'],
                    data['肺'],
                    data['肝脾'],
                    data['胃肠'],
                    data['脊柱'],
                    data['四肢'],
                    data['平足'],
                    data['皮肤'],
                    data['传染病'],
                    data['胸透'],
                    data['既往病史'],
                    data['医院名或校医名'],
                    data['其他1'],
                    data['其他2'],
                    data['其他3'],
                    data['其他4'],
                    data['其他5'],
                ])

        workbook1.save(f'./report/{self.fileName}专版明细表.xlsx')
        workbook.close()
        workbook1.close()
        return self.fashe(f"{self.fileName}专版明细表处理完毕！")

    # 填充
    def tc(self):
        workbook = openpyxl.load_workbook(f'./report/{self.fileName}专版明细表.xlsx')
        sheet = workbook['学生综合素质体检信息']
        max1 = sheet.max_row
        for i in range(5, max1 + 1):
            if sheet[f'U{i}'].value is None:
                sheet[f'U{i}'].value = "无"
            if sheet[f'V{i}'].value is None:
                sheet[f'V{i}'].value = '正常'
            if sheet[f'W{i}'].value is None:
                sheet[f'W{i}'].value = '无'
            if sheet[f'X{i}'].value is None:
                sheet[f'X{i}'].value = '正常'
            if sheet[f'Y{i}'].value is None:
                sheet[f'Y{i}'].value = '无'
            if sheet[f'Z{i}'].value is None:
                sheet[f'Z{i}'].value = '正常'
            if sheet[f'AA{i}'].value is None:
                sheet[f'AA{i}'].value = '未扪及'
            if sheet[f'AB{i}'].value is None:
                sheet[f'AB{i}'].value = '正常'
            if sheet[f'AC{i}'].value is None:
                sheet[f'AC{i}'].value = '正常'
            if sheet[f'AD{i}'].value is None:
                sheet[f'AD{i}'].value = '正常'
            if sheet[f'AE{i}'].value is None:
                sheet[f'AE{i}'].value = '无'
            if sheet[f'AF{i}'].value is None:
                sheet[f'AF{i}'].value = '正常'
            if sheet[f'AG{i}'].value is None:
                sheet[f'AG{i}'].value = '无'
            if sheet[f'AI{i}'].value is None:
                sheet[f'AI{i}'].value = '无'
            if sheet[f'AK{i}'].value is None:
                sheet[f'AK{i}'].value = '正常'
            if sheet[f'AL{i}'].value is None:
                sheet[f'AL{i}'].value = '正常'
            if sheet[f'AM{i}'].value is None:
                sheet[f'AM{i}'].value = '正常'
            if sheet[f'AN{i}'].value is None:
                sheet[f'AN{i}'].value = '正常'
            if sheet[f'AO{i}'].value is None:
                sheet[f'AO{i}'].value = '正常'
        workbook.save(f'./report/{self.fileName}专版明细表.xlsx')
        self.fashe('填充完毕！')


if __name__ == '__main__':
    starttime = datetime.datetime.today()
    app = QApplication(sys.argv)
    cn = School()
    bmi = bmi()
    cn.show()
    sys.exit(app.exec_())
